DECLARE @TMP TABLE(
	CODPREN CHAR(14)
)

INSERT INTO @TMP (CODPREN)
	SELECT distinct S.codpren FROM LINEE L (NOLOCK)
	INNER JOIN CONFIGLINEE CL (NOLOCK) ON  CL.CODLIN=L.CODLIN 
	INNER JOIN CORSE C (NOLOCK) ON C.COD_CFG_LINEA=CL.COD_CFG_LINEA
	INNER JOIN SEQBIG S (NOLOCK) ON S.CODCORSA=C.CODCORSA
	INNER JOIN BIGLIE B (NOLOCK) ON B.CODPREN=S.CODPREN AND B.N_MOD=S.N_MOD 
	WHERE L.MACROREGIONE='AL' AND B.ANNULLATO=1 AND S.CODPREN NOT IN ('IP991406250038')



DECLARE @CODPREN CHAR(14)
DECLARE CURSORE CURSOR FOR SELECT CODPREN FROM @TMP
OPEN CURSORE
FETCH NEXT FROM CURSORE INTO @CODPREN
WHILE @@FETCH_STATUS>=0
BEGIN

BEGIN TRANSACTION

---------------------------------------
--cancello le prenotazioni
---------------------------------------

PRINT N'Cancello i dati da passeg'

delete from passeg where codpren = @CODPREN
PRINT N'Cancello i dati da dettax'
delete from dettax where codpren = @CODPREN

print N'Cancello i dati da dettax_iva'
delete from dettax_iva where codpren=@codpren

PRINT N'Cancello i dati da biglietti'
delete from biglietti where codpren = @CODPREN
PRINT N'Cancello i dati da movimenti'

delete from movimenti where codpren = @CODPREN
PRINT N'Cancello i dati da detbig'

delete from detbig where codpren = @CODPREN

PRINT N'Cancello i dati da entbig'
delete from entbig where codpren = @CODPREN

PRINT N'Cancello i dati da seqbig'

delete from seqbig where codpren  = @CODPREN

PRINT N'Cancello i dati da biglie'

delete from biglie where codpren = @CODPREN

print N'Cancello i dati da commissioni_dett'

delete from commissioni_dett where codpren = @CODPREN

print N'commissioni_dett_pax'
delete from commissioni_dett_pax where codpren = @CODPREN

DELETE FROM JBOARD_PAX WHERE CODPREN=@CODPREN
DELETE FROM JBOARD_VEI WHERE CODPREN=@CODPREN

----------------------------------------
--cancello l'archivio delle prenotazioni
----------------------------------------

PRINT N'Cancello i dati da passeg_arch'

delete from passeg_arch where codpren = @CODPREN
PRINT N'Cancello i dati da dettax_arch'

delete from dettax_arch where codpren = @CODPREN

print N'Cancello i dati da dettax_iva_arch'
delete from dettax_iva_arch where codpren=@codpren

PRINT N'Cancello i dati da movimenti_arch'

delete from movimenti_arch where codpren = @CODPREN

PRINT N'Cancello i dati da detbig_arch'

delete from detbig_arch where codpren = @CODPREN

PRINT N'Cancello i dati da entbig_arch'

delete from entbig_arch where codpren = @CODPREN

PRINT N'Cancello i dati da seqbig_arch'

delete from seqbig_arch where codpren = @CODPREN

PRINT N'Cancello i dati da biglie_arch'

delete from biglie_arch where codpren = @CODPREN

print N'Cancello i dati da archivia_biglietti'

delete from archivia_biglietti where codpren = @CODPREN

PRINT N'CANCELLO DA JBOARD'
DELETE FROM JBOARD_PAX_ARCH WHERE CODPREN = @CODPREN
DELETE FROM JBOARD_VEI_ARCH WHERE CODPREN = @CODPREN

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN ROLLBACK TRANSACTION END
ELSE
COMMIT TRANSACTION

FETCH NEXT FROM CURSORE INTO @CODPREN
END
CLOSE CURSORE
DEALLOCATE CURSORE
